﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.IO;
namespace F1010DB
{
    public class BaseDatabase : ConnectionToDB
    {
        private const string SQL_CONNECTION_FILE = "connect.txt";
        private const string CONNECT_TEMPLATE = "Data Source={0};Initial Catalog={1};User Id=sa;Password=P3#t@rVP; Connect Timeout=30;";
        private SqlConnection connect = null;
        private string getString = string.Empty;
        private SqlCommand comm;
        private SqlDataAdapter dad;
        private SqlDataReader dr;
        private DataTable dt;
        private DataView dv;

        protected override string setStringSQLServer()
        {
            settingSQLConnection();
            //initSetUpFileConnection(); 
            return getString;
        }

        private void settingSQLConnection()
        {
            getString = String.Format(CONNECT_TEMPLATE, @"103.27.239.204", "F10101", "True");
        }
        public void initSetUpFileConnection()
        {
            if (!File.Exists(SQL_CONNECTION_FILE)) { return; }

            List<String> sLine = File.ReadAllLines(SQL_CONNECTION_FILE).ToList();
            Dictionary<string, string> dServerInformation = new Dictionary<string, string>();
 
            if (sLine.Count != 3)
            {
                throw new Exception("This file is not invalid");
            }
            foreach (string s in sLine)
            {
                if (!s.Contains("="))
                {
                    dServerInformation = null;
                    throw new Exception("This file is not invalid");
                }
                string[] sGroup = s.Split('=');

                if (sGroup.Length > 2)
                {
                    dServerInformation = null;
                    throw new Exception("This file is not invalid");
                }

                if (!sGroup[0].Equals("DATA SOURCE") || !sGroup[0].Equals("INITIAL CATALOG") || !sGroup.Equals("Integrated Security"))
                {
                    dServerInformation = null;
                    throw new Exception("This file is not invalid");
                }

                dServerInformation.Add(sGroup[0], sGroup[1]);
            }

            getString = String.Format(CONNECT_TEMPLATE, dServerInformation["DATA SOURCE"], dServerInformation["INITIAL CATALOG"], dServerInformation["Integrated Security"]);
        }

        private SqlCommand setCommand(string sqlString, System.Data.CommandType type, Dictionary<string, object> parameters)
        {
            SqlCommand comm1 = new SqlCommand();
            
            comm1 = new SqlCommand();
            comm1.CommandText = sqlString;
            comm1.CommandType = type;

            if (parameters != null)
            {
                foreach (string key in parameters.Keys)
                {
                    comm1.Parameters.Add(new SqlParameter(key, parameters[key]));
                }
            }
            connect = openConnect();

            if (connect == null) { return null; }

            //comm1.Connection = connect;
            return comm1;
        }
        
        /// <summary>
        /// Phương thức dùng để execute đa câu lệnh SQL
        /// </summary>
        /// <param name="entityCommand"></param>
        /// <returns></returns>
        public bool executeNonQuery(SetSQLEntityCommand[] entityCommand)
        {
          //  SqlTransaction transaction = null;
            bool isSucceed = false;

            try
            {
                connect = openConnect();
             //   transaction = connect.BeginTransaction();

                for(int i= 0; i < entityCommand.Length; i++)
                {
                    SetSQLEntityCommand item = entityCommand[i];

                    SqlCommand comm1 = setCommand(item.sql, item.type, item.dicPara);
                    comm1.Connection = connect;
            //        comm1.Transaction = transaction;
                    if (comm1.ExecuteNonQuery() <= 0)
                    {
                        isSucceed = false;
                        break;
                    }
                    
                }

                isSucceed = true;
            //    transaction.Commit();
            }
            catch(SqlException sqlEx)
            {
             //   transaction.Rollback();
                throw new Exception(sqlEx.Message);
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (isSucceed == false)
                {
               //     transaction.Rollback();
                }
                //transaction.Dispose();
                close(connect);
            }

            return isSucceed;
        }

        public bool executeNonQuery(string sqlString, System.Data.CommandType type, Dictionary<string, object> parameters)
        {
            SqlTransaction transaction = null;
            bool isSucceed = false;
            try
            {
                comm = setCommand(sqlString, type, parameters);

                if (comm == null)
                {
                    isSucceed = false;
                }
                else
                {
                    transaction = connect.BeginTransaction();
                    comm.Connection = connect;
                    comm.Transaction = transaction;

                    int iCommandResult = comm.ExecuteNonQuery();

                    if (iCommandResult > 0)
                    {
                        transaction.Commit();
                        isSucceed = true;
                    }
                }
            }
            catch (SqlException ex)
            {
                transaction.Rollback();
                throw new Exception(ex.Message);
            }
            catch (Exception ex1)
            {
                throw new Exception(ex1.Message);
            }
            finally
            {
                if (isSucceed == false && transaction != null)
                {
                    transaction.Rollback();
                }
                transaction.Dispose();
                close(connect);
            }

            return isSucceed;
        }

        public System.Data.DataTable readATable(string sqlString, System.Data.CommandType type, Dictionary<string, object> parameters)
        {
            comm = setCommand(sqlString, type, parameters);

            if (comm == null)
            {
                return null;
            }
            else
            {
                comm.Connection = connect;
                dad = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();

                dad.Fill(ds);
                dt = new DataTable();
                dt = ds.Tables[0];
            }

            close(connect);
            return dt;
        }

        public System.Data.DataView readDataView(string sqlString, System.Data.CommandType type, Dictionary<string, object> parameters)
        {
            comm = setCommand(sqlString, type, parameters);

            if (comm == null)
            {
                return null;
            }
            else
            {
                comm.Connection = connect;
                dad = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();
                
                dad.Fill(ds);
                dv = new DataView(ds.Tables[0]);
            }
            close(connect);
            return dv;
        }

        public SqlDataReader readDataRead(string sqlString, System.Data.CommandType type, Dictionary<string, object> parameters)
        {
            comm = setCommand(sqlString,type,parameters);

            if (comm == null)
            {
                dr = null;
            }
            else
            {
                comm.Connection = connect;
                dr = comm.ExecuteReader();
            }

            close(connect);
            return dr;
        }
    }

    public class SetSQLEntityCommand
    {
        public string sql;
        public CommandType type;
        public Dictionary<string, object> dicPara;
    }
}
